***** ANALYZE ********
clear all
set more off

cd "$path"
*** FIGURE OUT WHERE TO ADD/SUBTRACT GUARDS ***

use "Data/proprietary/01 Master Data" if neighbor==1, clear
drop chiave_agenzia2 km_to_nid unsuccessful2 attempted2 guardia2 flag2 codiceabi2 /*
*/ninvisible2 nvisible2 
sort chiave_agenzia1 year
tsset chiave_agenzia1 year 

gen chara1=nvisible1+ninvisible1
drop if chara1==0 

 
sort chiave_agenzia1 year

gen glat=int(latitude/0.01/50)
gen glong=int(longitude/0.01/50)
bysort glat glong year: gen x=_n==1
gen cluster50=sum(x)
drop x glat glong
save temp, replace

foreach nmarket in .5 {
local n="p5"
 use temp, clear
 gen glat=int(latitude/0.01/`nmarket')
 gen glong=int(longitude/0.01/`nmarket')

 bysort glat glong: gen x=_n==1
 gen ggroup`n'=sum(x)
 drop x 
 
 *sort ggroup`n' year
 
 egen guard_tot=sum(guardia1), by(ggroup`n' year)
 egen bank_tot=count(ggroup`n'), by(ggroup`n' year)
 gen guardia01=((guard_tot-guardia1)/(bank_tot-1))
 replace guardia01=0 if guardia01==.

 gen guardia11=((guard_tot-guardia1)/(bank_tot-1))*(guardia1)
 gen no_substitutes=guardia11==.
 replace guardia11=0 if guardia11==.
 
 egen chara1_tot=sum(chara1), by(ggroup`step' year)
 egen ninvisible1_tot=sum(ninvisible1), by(ggroup`step' year)
 egen nvisible1_tot=sum(nvisible1), by(ggroup`step' year)
 egen attempted1_tot=sum(attempted1), by(ggroup`step' year)
 

 gen chara2=(chara1_tot-chara1)/(bank_tot-1)
 gen ninvisible2=(ninvisible1_tot-ninvisible1)/(bank_tot-1) 
 gen nvisible2=(nvisible1_tot-nvisible1)/(bank_tot-1)
 gen ninvisible2_tot=(ninvisible1_tot-ninvisible1)/100
 gen attempted2=(attempted1_tot-attempted1)/(bank_tot-1)

 replace nvisible2=0 if nvisible2==.
 replace ninvisible2=0 if ninvisible2==.
 replace chara2=0 if chara2==.
 
 drop chara1_tot ninvisible1_tot nvisible1_tot
 rename idprov idprovincia

 reghdfe attempted1 guardia1 guardia01 guardia11 no_substitutes chara1 chara2, /*
 */absorb(ALLFE=ggroup`step'#c.year YEAR=year) resid
 predict hrobberies, xbd
 *sort ggroup`n' year
 gen benALL_`n'_disagg= (1-guardia1)*_b[guardia1]+(1-guardia01)*_b[guardia01]+(1-guardia11)*_b[guardia11]
 gen benALL_`n'_disagg_LO= (1-guardia1)*(_b[guardia1]-1.96*_se[guardia1])+(1-guardia01)*(_b[guardia01]-1.96*_se[guardia01])+(1-guardia11)*(_b[guardia11]-1.96*_se[guardia11])
 gen benALL_`n'_disagg_HI= (1-guardia1)*(_b[guardia1]+1.96*_se[guardia1])+(1-guardia01)*(_b[guardia01]+1.96*_se[guardia01])+(1-guardia11)*(_b[guardia11]+1.96*_se[guardia11])

 gen benNONE_`n'_disagg= (0-guardia1)*_b[guardia1]+(0-guardia01)*_b[guardia01]+(0-guardia11)*_b[guardia11]
 gen benNONE_`n'_disagg_LO= (0-guardia1)*(_b[guardia1]-1.96*_se[guardia1])+(0-guardia01)*(_b[guardia01]-1.96*_se[guardia01])+(0-guardia11)*(_b[guardia11]-1.96*_se[guardia11])
 gen benNONE_`n'_disagg_HI= (0-guardia1)*(_b[guardia1]+1.96*_se[guardia1])+(0-guardia01)*(_b[guardia01]+1.96*_se[guardia01])+(0-guardia11)*(_b[guardia11]+1.96*_se[guardia11])

 egen tot_benALL_`n'_disagg=sum(benALL_`n'_disagg), by(ggroup`n' year)
 egen tot_benALL_`n'_disagg_LO=sum(benALL_`n'_disagg_LO), by(ggroup`n' year)
 egen tot_benALL_`n'_disagg_HI=sum(benALL_`n'_disagg_HI), by(ggroup`n' year)

 egen tot_benNONE_`n'_disagg=sum(benNONE_`n'_disagg), by(ggroup`n' year)
 egen tot_benNONE_`n'_disagg_LO=sum(benNONE_`n'_disagg_LO), by(ggroup`n' year)
 egen tot_benNONE_`n'_disagg_HI=sum(benNONE_`n'_disagg_HI), by(ggroup`n' year)

 keep chiave_agenzia1 year ggroup`n' tot_ben* hrobberies
 tempfile temp`n'
 sort chiave_agenzia1 year
 save `temp`n'', replace
}


compress
sort chiave_agenzia1 year
save "Data/proprietary/2 Simulation Data AllNone guards", replace
***************************************


* skip
use "Data/proprietary/01 Master Data" if neighbor==1, clear
drop if longitude==0
keep  chiave_agenzia1 year longitude latitude comune provincia attempted1 guardia1 nvisible1 ninvisible1
sort chiave_agenzia1 year

merge 1:1 chiave_agenzia1 year using  "Data/proprietary/2 Simulation data AllNone guards"
drop _m

tempfile temp
gen id=_n
sort id
save `temp', replace

clear
insheet using "Data\non-proprietary\Export_Output_2.txt", comma
keep id point*
sort id

merge id using `temp'
tab _m
drop _m

geoinpoly latitude longitude using "Data\non-proprietary\italy_coord_grid25", unique noprojection
ren _ID ID

bysort chiave_agenzia1 (year): gen hiring=guardia1[_n-1]==0&guardia1==1 
bysort chiave_agenzia1 (year): gen firing=guardia1[_n-1]==1&guardia1==0 

gen all=1


spmap using "Data/non-proprietary/italy-outlinecoordinates",      ///
         id(id) point(proportional(all) by(attempted1) xcoord(point_x ) ycoord(point_y) size(vtiny tiny) fcolor(gs11))  fcolor(white)                 ///
         legend(off) ti("Distribution of Robberies")
		 graph export "$path/figures/attempted.png", replace
		 
spmap using "Data/non-proprietary/italy-outlinecoordinates",      ///
         id(id) point(proportional(all) by(guardia1) xcoord(point_x) ycoord(point_y) size(vtiny tiny) fcolor(gs11))  fcolor(white)                 ///
         legend(off) ti("Distribution of Guards")
		 graph export "$path/figures/guards.png", replace

spmap using "Data/non-proprietary/italy-outlinecoordinates",      ///
         id(id) point(proportional(all) by(firing) xcoord(point_x) ycoord(point_y) size(vtiny tiny) fcolor(gs11))  fcolor(white)                 ///
         legend(off) ti("Distribution of Firing of Guards")
		 graph export "$path/figures/firing.png", replace

spmap using "Data/non-proprietary/italy-outlinecoordinates",      ///
         id(id) point(proportional(all) by(hiring) xcoord(point_x) ycoord(point_y) size(vtiny tiny) fcolor(gs11))  fcolor(white)                 ///
         legend(off) ti("Distribution of Hiring of Guards")
		 graph export "$path/figures/hiring.png", replace


rename tot_benALL_p5_disagg tot_benALL
rename tot_benNONE_p5_disagg tot_benNONE
collapse (sum) tot_benALL tot_benNONE hrobberies, by(ID)
replace hrobberies=0 if hrobberies<0
replace tot_benALL=min(-tot_benALL, hrobberies)
gen hfrac_ALL=min(tot_benALL/hrobberies,1)
gen hfrac_NONE=tot_benNONE/hrobberies


count if tot_benALL >100  
        local count4 = r(N)
count if tot_benALL >50 & hfrac_NONE <=100 
        local count3 = r(N)
count if tot_benALL >10 & hfrac_NONE <=50
        local count2 = r(N)
count if tot_benALL <=10
        local count1 = r(N)

spmap tot_benALL using "Data/non-proprietary/italy_coord_grid25",      ///
         id(ID)              ///
         osize(vvthin vvthin vvthin vvthin) /*
*/		 clmethod(custom) clbreak(0 10 50 100 40000)  /*
*/        legend(label(5 "More than 100    (`count4') ")) /*
*/        legend(label(4 "50 to 100        (`count3') ")) /*
*/        legend(label(3 "10 to 50      (`count2') ")) /*
*/        legend(label(2 "Fewer than 10 (`count1') ")) /*
*/		  legtitle("Decrease in Robberies")  
		 graph export "Figures\benefitsALL25.png", replace


count if hfrac_ALL >-.05  
        local count4 = r(N)
count if hfrac_ALL >-.1 & hfrac_NONE <=-.05 
        local count3 = r(N)
count if hfrac_ALL >-.25 & hfrac_NONE <=-.1 
        local count2 = r(N)
count if hfrac_ALL <-.25 
        local count1 = r(N)

spmap hfrac_ALL using "Data/non-proprietary/italy_coord_grid25",      ///
         id(ID)              ///
         osize(vvthin vvthin vvthin vvthin) /*
*/		 clmethod(custom) clbreak(0 0.25 0.50 0.75 1)  /*
*/        legend(label(5 "More than 75%    (`count4') ")) /*
*/        legend(label(4 "50% to 75%        (`count3') ")) /*
*/        legend(label(3 "25% to 50%      (`count2') ")) /*
*/        legend(label(2 "Less than 25% (`count1') ")) /*
*/		  legtitle("Decrease in Robberies")  
		 graph export "Figures\benefits_frac_ALL25.png", replace


count if tot_benNONE >50  
        local count4 = r(N)
count if tot_benNONE >10 & hfrac_NONE <=50 
        local count3 = r(N)
count if tot_benNONE >5 & hfrac_NONE <=10
        local count2 = r(N)
count if tot_benNONE <=5
        local count1 = r(N)

spmap tot_benNONE using "Data/non-proprietary/italy_coord_grid25",      ///
         id(ID)              ///
         osize(vvthin vvthin vvthin vvthin) /*
*/		 clmethod(custom) clbreak(-1 5 10 50 400)  /*
*/        legend(label(5 "More than 50    (`count4') ")) /*
*/        legend(label(4 "10 to 50        (`count3') ")) /*
*/        legend(label(3 "5 to 10      (`count2') ")) /*
*/        legend(label(2 "Fewer than 5 (`count1') ")) /*
*/		  legtitle("Increase in Robberies")  
		 graph export "Figures\benefitsNONE25.png", replace

		 
		 
count if hfrac_NONE >1
        local count4 = r(N)
count if hfrac_NONE >-0.5 & hfrac_NONE <=1 
        local count3 = r(N)
count if hfrac_NONE >.1 & hfrac_NONE <=0.5 
        local count2 = r(N)
count if hfrac_NONE <=.1 
        local count1 = r(N)

spmap hfrac_NONE using "Data/non-proprietary/italy_coord_grid25",      ///
         id(ID)              ///
         osize(vvthin vvthin vvthin vvthin) /*
*/		 clmethod(custom) clbreak(0 0.1 .5 1 40000)  /*
*/        legend(label(5 "More than 100%    (`count4') ")) /*
*/        legend(label(4 "50% to 100%        (`count3') ")) /*
*/        legend(label(3 "10% to 50%      (`count2') ")) /*
*/        legend(label(2 "Less than 10% (`count1') ")) /*
*/		  legtitle("Increase in Robberies")  
		 graph export "Figures\benefits_frac_NONE25.png", replace
		 
		

/****** COUNTERFACTUAL CHANGE IN ROBBERIES IF GUARDS AT THEIR 2000 LEVELS****/

use "Data/proprietary/01 Master Data" if neighbor==1, clear
drop chiave_agenzia2 km_to_nid unsuccessful2 attempted2 guardia2 flag2 codiceabi2 /*
*/ninvisible2 nvisible2 /* mguardia2 mnvisible2 mninvisible2 */
sort chiave_agenzia1 year
tsset chiave_agenzia1 year 

gen chara1=nvisible1+ninvisible1
drop if chara1==0 

 
sort chiave_agenzia1 year

gen glat=int(latitude/0.01/50)
gen glong=int(longitude/0.01/50)
bysort glat glong year: gen x=_n==1
gen cluster50=sum(x)
drop x glat glong
save temp, replace

foreach nmarket in .5 {
local n="p5"
 use temp, clear
 gen glat=int(latitude/0.01/`nmarket')
 gen glong=int(longitude/0.01/`nmarket')

 bysort glat glong: gen x=_n==1
 gen ggroup`n'=sum(x)
 drop x 
 
 *sort ggroup`n' year
 
 egen guard_tot=sum(guardia1), by(ggroup`n' year)
 egen bank_tot=count(ggroup`n'), by(ggroup`n' year)
 gen guardia01=((guard_tot-guardia1)/(bank_tot-1))
 replace guardia01=0 if guardia01==.

 gen guardia11=((guard_tot-guardia1)/(bank_tot-1))*(guardia1)
 gen no_substitutes=guardia11==.
 replace guardia11=0 if guardia11==.
 
 egen chara1_tot=sum(chara1), by(ggroup`step' year)
 egen ninvisible1_tot=sum(ninvisible1), by(ggroup`step' year)
 egen nvisible1_tot=sum(nvisible1), by(ggroup`step' year)
 egen attempted1_tot=sum(attempted1), by(ggroup`step' year)
 

 gen chara2=(chara1_tot-chara1)/(bank_tot-1)
 gen ninvisible2=(ninvisible1_tot-ninvisible1)/(bank_tot-1) 
 gen nvisible2=(nvisible1_tot-nvisible1)/(bank_tot-1)
 gen ninvisible2_tot=(ninvisible1_tot-ninvisible1)/100
 gen attempted2=(attempted1_tot-attempted1)/(bank_tot-1)

 replace nvisible2=0 if nvisible2==.
 replace ninvisible2=0 if ninvisible2==.
 replace chara2=0 if chara2==.
 
 drop chara1_tot ninvisible1_tot nvisible1_tot
 rename idprov idprovincia
 
 sort chiave_agenzia1 year
 foreach var of varlist guardia1 guardia01 guardia11 {
  gen `var'_2000=`var'
  replace `var'_2000=`var'[_n-1] if chiave_agenzia1==chiave_agenzia1[_n-1]
 }

 reghdfe attempted1 guardia1 guardia01 guardia11 no_substitutes chara1 chara2, /*
 */absorb(ALLFE=ggroup`step'#c.year YEAR=year) resid
 predict hrobberies, xbd
 
 gen hrobberies_counterfactual=hrobberies
 foreach var of varlist guardia1 guardia01 guardia11 {
  replace hrobberies_counterfactual=hrobberies_counterfactual+(`var'_2000-`var')*_b[`var']
 }
}


/*****/



use "data/proprietary/chiave_codice.dta", clear
sort chiave_agenzia
tempfile banks
save `banks', replace

use "Data/proprietary/00 Merged Data", clear
sort chiave_agenzia
merge chiave_agenzia using `banks'
drop _merge
egen med_haul=median(haul), by(year prov)

tempfile hauls
rename chiave_agenzia chiave_agenzia1
sort chiave_agenzia1 year
save `hauls', replace

tempfile temp
use "Data/proprietary/01 Master Data" if neighbor==1, clear
drop chiave_agenzia2 km_to_nid unsuccessful2 attempted2 guardia2 flag2 codiceabi2 /*
*/ninvisible2 nvisible2 /* mguardia2 mnvisible2 mninvisible2 */
*use "temp", clear
sort chiave_agenzia1 year
tsset chiave_agenzia1 year 

gen chara1=nvisible1+ninvisible1
drop if chara1==0 

sort chiave_agenzia1 year
merge chiave_agenzia1 year using `hauls'
drop _merge


 
save `temp', replace


/*** FIGURE OUT WHERE TO ADD/SUBTRACT GUARDS ***/

local tot0=0.5
local tot1=0.5
local step=1

noi {

 use `temp', clear
 
 gen glat=int(latitude/0.01/0.5)      //500m squares only!
 gen glong=int(longitude/0.01/0.5)
 bysort glat glong: gen x=_n==1
 gen ggroup=sum(x)
 drop x glat glong
 
 sort chiave_agenzia1 year

 egen guard_tot=sum(guardia1), by(ggroup year)
 egen bank_tot=count(ggroup), by(ggroup year)
 
 gen guardia01=((guard_tot-guardia1)/(bank_tot-1)) // *(1-guardia1) 
 replace guardia01=0 if guardia01==.
 gen guardia11=((guard_tot-guardia1)/(bank_tot-1))*(guardia1)
 gen no_substitutes=guardia11==.
 replace guardia11=0 if guardia11==.

 egen chara1_tot=sum(chara1), by(ggroup year)
 egen attempted1_tot=sum(attempted1), by(ggroup year)
 
 gen chara2=(chara1_tot-chara1)/(bank_tot-1)

 replace chara2=0 if chara2==.
 
 drop chara1_tot
 rename idprov idprovincia
 
 gen guardia1ADD=guardia1
 gen guardia1SUB=guardia1

 sort year ggroup guardia1
 
 replace guardia1ADD=1 if ggroup!=ggroup[_n-1]
 replace guardia1SUB=0 if ggroup!=ggroup[_n+1]
 sort ggroup year
 
 foreach x in "ADD" "SUB" {
  egen guard_tot`x'=sum(guardia1`x'), by(ggroup year)
  egen bank_tot`x'=count(ggroup), by(ggroup year)
  gen guardia01`x'=((guard_tot`x'-guardia1`x')/(bank_tot-1))*(1-guardia1`x')
  gen guardia11`x'=((guard_tot`x'-guardia1`x')/(bank_tot-1))*(guardia1`x')
  replace guardia01`x'=0 if guardia01`x'==.
  replace guardia11`x'=0 if guardia11`x'==.
 }
 reghdfe attempted1 guardia1 guardia01 guardia11 chara1 chara2 no_substitutes, absorb(ggroup#c.year year) cluster(ggroup)
 sort ggroup year
 foreach x in "ADD" "SUB" {
  gen ben`x'= (guardia1`x'-guardia1)*_b[guardia1]+(guardia01`x'-guardia01)*_b[guardia01]+(guardia11`x'-guardia11)*_b[guardia11]
  gen ben`x'_LO= (guardia1`x'-guardia1)*(_b[guardia1]-1.96*_se[guardia1])+(guardia01`x'-guardia01)*(_b[guardia01]-1.96*_se[guardia01])+(guardia11`x'-guardia11)*(_b[guardia11]-1.96*_se[guardia11])
  gen ben`x'_HI= (guardia1`x'-guardia1)*(_b[guardia1]+1.96*_se[guardia1])+(guardia01`x'-guardia01)*(_b[guardia01]+1.96*_se[guardia01])+(guardia11`x'-guardia11)*(_b[guardia11]+1.96*_se[guardia11])
  
  egen tot_ben`x'_=sum(ben`x'), by(ggroup year)
  egen tot_ben`x'_LO_=sum(ben`x'_LO), by(ggroup year)
  egen tot_ben`x'_HI_=sum(ben`x'_HI), by(ggroup year)
 }  

 foreach var of varlist tot_ben* {
  gen `var'HAUL=`var'*med_haul
 }
 
 keep chiave_agenzia1 year ggroup tot_ben* codice_abi
}
save "data/proprietary/2 Simulation data AddSub guards", replace


use "data/proprietary/2 Simulation data AddSub guards", clear
keep if year==2005

duplicates drop year ggroup, force
 foreach stub in "_" "_HAUL" {
  foreach x in "ADD" "SUB" {
   preserve
   drop if ("`x'"=="ADD" & tot_ben`x'`stub'>=0) | ("`x'"=="SUB" & tot_ben`x'`stub'<=0)
   egen rank`x'=rank(tot_ben`x'`stub'), unique
   sort rank`x'
   if "`x'"=="ADD" {
    local xt="Added" 
	local xt2="Reduction"
    replace tot_ben`x'`stub'=-1*tot_ben`x'`stub'
	replace tot_ben`x'_LO`stub'=-1*tot_ben`x'_LO`stub'
	replace tot_ben`x'_HI`stub'=-1*tot_ben`x'_HI`stub'
   }
   if "`x'"=="SUB" {
    local xt="Removed" 
	local xt2="Increase"
   }
   replace tot_ben`x'`stub'=tot_ben`x'`stub'+tot_ben`x'`stub'[_n-1] if year==year[_n-1]
   replace tot_ben`x'_LO`stub'=tot_ben`x'_LO`stub'+tot_ben`x'_LO`stub'[_n-1] if year==year[_n-1]
   replace tot_ben`x'_HI`stub'=tot_ben`x'_HI`stub'+tot_ben`x'_HI`stub'[_n-1] if year==year[_n-1]
   gen z=0   
   
   if "`stub'"=="_" {
    local yt="`xt2' in Robberies (Simulated)"
   }
   if "`stub'"=="_HAUL" {
    replace tot_ben`x'`stub'=tot_ben`x'`stub'/1000
    local yt="`xt2' in Total Amount Robbed (Thousands)"
   }
   
   twoway line tot_ben`x'`stub' rank`x', sort xtitle("Num. Markets with Guards `xt'") ytitle("`yt'") scheme(s1mono) legend(off)
   graph export "figures/sim `x'500m`stub'.pdf", replace
   restore
  }
 }
 

use "data/proprietary/2 Simulation data AddSub guards", clear
keep if year==2005

replace tot_benADD_=-1*tot_benADD_
      
egen red_from_ADD=max(tot_benADD_), by(codice_abi)
egen inc_from_SUB=max(tot_benSUB_), by(codice_abi)
gen ben_from_switch=red_from_ADD-inc_from_SUB
duplicates drop year codice_abi, force
   
drop if ben_from_switch<=0
replace ben_from_switch=-ben_from_switch
sort ben_from_switch
egen rank=rank(ben_from_switch), unique
replace ben_from_switch=-ben_from_switch
twoway bar ben_from_switch rank, xtitle("Num. Banks That Swap Guards") ytitle("Reduction in Robberies (Simulated)") scheme(s1mono) legend(off)
graph export "figures/sim 500m bank switch.pdf", replace


